<?php

/**
 *
 *	@author			
 *	@copyright		
 *	@package		
 *		
 *	@filesource		
 *	@date			
 *
 */

// Deny opening in browser
if (!defined("PUBLIC_HTML")) 
{	
	exit;	
}



// Create the Initial Database class
/**
 * DatabaseInit
 * 
 * @package   
 * @author CMS
 * @copyright Christopher Hazekamp
 * @version 2010
 * @access public
 */
class DatabaseInit {
	
	// Initiate variables
	private $host;
	private $user;
	private $pass;
	private $name;
	
	private $connect;
	private $selectdb;
	
	/***
	 * Constructor
	 * - Check if database data is right
	 * - Make a database connection
	 *
	 * DatabaseInit::__construct()
	 * 
	 * @param mixed $dbinfo
	 * @return
	 */
	function __construct (array $dbinfo) 
	{			
		foreach ($dbinfo as $key => $value)
		{
			$this->$key = $value;
		}

		$this->connect		= mysqli_connect($this->host, $this->user, $this->pass);
		if (!$this->connect)
		{
			throw new ErrorHandler('Er kon geen verbinding worden gemaakt met de database');
		}
		
		$this->selectdb		= mysqli_select_db($this->name);	
		if (!$this->selectdb)
		{
			throw new ErrorHandler('De opgegeven database bestaat niet of is niet toegankelijk');
		}
		
	}
	
	// Function for SQL Error
	/**
	 * DatabaseInit::sql_error()
	 * 
	 * @return
	 */
	private function sql_error() 
	{	
		return mysqli_error();	
	}
	
	// Function for SQL Queries
	/**
	 * DatabaseInit::sql_query()
	 * 
	 * @param mixed $query
	 * @return
	 */
	public function sql_query($query) 
	{	
		/*if (!@mysqli_query($query))
		{
			throw new ErrorHandler($this->sql_error());
		}*/
		return mysqli_query($query);
	}
	
	// Function for getting results as an associative array
	/**
	 * DatabaseInit::sql_fetch_assoc()
	 * 
	 * @param mixed $fetchAssoc
	 * @return
	 */
	public function sql_fetch_assoc($fetchAssoc) 
	{	
		return mysqli_fetch_assoc($fetchAssoc);
	}

	// Function for getting results as a general array
	// which can be called on both numeric as associative
	/**
	 * DatabaseInit::sql_fetch_array()
	 * 
	 * @param mixed $fetchArray
	 * @return
	 */
	public function sql_fetch_array($fetchArray) 
	{	
		return mysqli_fetch_array($fetchArray);
	}
	
	// Function to empty the filled memory
	/**
	 * DatabaseInit::sql_free_result()
	 * 
	 * @param mixed $freeResult
	 * @return
	 */
	public function sql_free_result($freeResult) 
	{	
		if (!mysqli_free_result($freeResult))
		{
			throw new ErrorHandler($this->sql_error());
		}
		return mysqli_free_result($freeResult);
	}
	
	// function for counting the rows
	/**
	 * DatabaseInit::sql_num_rows()
	 * 
	 * @param mixed $numRows
	 * @return
	 */
	public function sql_num_rows($numRows) 
	{	
		return mysqli_num_rows($numRows);
	}
	
	// Function for getting the ID of the newly inserted data
	/**
	 * DatabaseInit::sql_insert_id()
	 * 
	 * @return
	 */
	public function sql_insert_id()
	{
		return mysqli_insert_id();
	}
	
	/***
	 * Destructor
	 * - Closes conneciton
	 * - Destroys connection
	 * - Destroys all login data
	 ***/
	/**
	 * DatabaseInit::__destruct()
	 * 
	 * @return
	 */
	function __destruct ()
    {
        if(isset($this->connect))
        {
            mysqli_close($this->connect);
            unset($this->connection);
			unset($this->db_host);
			unset($this->db_user);
			unset($this->db_name);
			unset($this->db_pass);
        }
    } 
	
}

// Extend the basis class with more advanced functions
/**
 * Database
 * 
 * @package   
 * @author CMS
 * @copyright Christopher Hazekamp
 * @version 2010
 * @access public
 */
class Database extends DatabaseInit
{
	
	/***
	 * Function for counting the rows in a table
	 * Required:
	 * - $table: The name of the table where you want to count stuff
	 * 
	 * Optional:
	 * - $condition: The name of the field where the count has to be based on
	 * - $value: The value given with the condition
	 * 
	 * Example:
	 * $amount_members = $mydb->sql_count('members');
	 ***/
	/**
	 * Database::sql_count()
	 * 
	 * @param mixed $table
	 * @param string $condition
	 * @param string $value
	 * @param string $operator
	 * @return
	 */
	public function sql_count($table, $condition='', $value='', $operator='=')
	{
		// Check if a condition and value have been given
		// if so, WHERE conditions will be used
		if (!empty($condition) && !empty($value))
		{
			// Check if the condition and value have been given as an array
			if (is_array($condition) && is_array($value))
			{
			// Compare the amount of keys in both arrays
				if (!count($condition) == count($value))
				{
					// Throw an exception if the comparison returns false
					throw new ErrorHandler('Condition and Value arrays must match in size!');
				}
				else
				{
					// Count the amount of keys given
					// We use the condition array, but the value array could have been used as well
					$condition_count = count($condition);
					
					// Compare the operator count with those of the conditions
					// If the operator is an array
					if (is_array($operator) && !count($operator) == $condition_count)
					{
						// Throw an exception if the comparison returns false
						throw new ErrorHandler('Operator array count must match with that of conditions and values');
					}
					else
					{
						// Initiate the query
						$query = "SELECT COUNT(*) AS amount FROM ".$table." ";
						
						// Prepare looping values
						$i_condition 	= 0;
						$i_value		= 0;
						$i_operator		= 0;
						
						// Loop all conditions into a new array
						foreach ($condition as $key)
						{
							$a_condition[$i_condition] = $key;
							$i_condition++;
						}
						
						// Loop all values into a new array
						foreach ($value as $key)
						{
							$a_value[$i_value] = $key;
							$i_value++;
						}
						
						// Loop all operators into a new array
						// if the operator is an array
						if (is_array($operator))
						{
							foreach ($operator as $key)
							{
								$a_operator[$i_operator] = $key;
								$i_operator++;
							}
						}
						
						// Now loop all arrays and finish our query
						for ($i = 0; $i < $i_condition; $i++)
						{
							// Decide what the actual operator will be
							$this_operator = (is_array($operator)) ? $a_operator[$i] : $operator;
							
							if ($i == 0)
							{
								$query .= "WHERE ".$a_condition[$i]." ".$this_operator." '".$a_value[$i]."'";
							}
							else
							{
								$query .= "AND ".$a_condition[$i]." ".$this_operator." '".$a_value[$i]."'";
							}
						} // End: for
					} // End: if
				} //End: if
			}
			else
			{
				$query = "SELECT COUNT(*) AS amount FROM ".$table." WHERE ".$condition." ".$operator." '".$value."'";
			} // End: if     
		}
		else
		{
			$query = "SELECT COUNT(*) AS amount FROM ".$table;
		} // End: if 
		try
		{
			$sql	= parent::sql_query($query);
		}
		catch (ErrorHandler $e)
		{
			echo $e->print_error();
		}
		$row	= parent::sql_fetch_assoc($sql);
		return $row['amount'];
	}
	
	/***
	 * Function for sql injection prevention
	 * Required
	 * - $value: The string that has to be checked
	 * 
	 * Example:
	 * $title = $mydb->sql_injection($_POST['title']);
	 */
	/**
	 * Database::sql_injection()
	 * 
	 * @param mixed $value
	 * @return
	 */
	public function sql_injection($value)
    {
        $magic_quotes_active = get_magic_quotes_gpc();
        $php_support = function_exists("mysqli_real_escape_string"); //check if PHP is V4.3.0 or higher
        if($php_support)
        {
            //delete slashes (magic quotes) so mysqli_real_escape_string can do the work
            if($magic_quotes_active)
            {
                $value = stripslashes($value);
            }
            $value = mysqli_real_escape_string($value);
        }
        else
        {
			//if there are no slashes (magic quotes) then add them before insertion into mysql
			if(!$magic_quotes_active)
			{
				$value = addslashes($value);
			}
			//if there are slashes (magic quotes) then do nothing
        }
        return $value;
    }
    
    public function sql_get($table, $fields = '', $where_con = '', $where_val = '', $limit = 0, $offset = 0, $order_by = '', $order_dir = '')
    {
    	$query_fields 	= '';
    	$query_where 	= '';
    	$query_limit 	= '';
    	$query_order 	= '';
    	$i_rows = 0;
    	
    	// Set the fields
    	if (is_array($fields))
    	{
  			$query_fields .= implode(',', $fields);
    	}
    	elseif (!empty($fields))
    	{
    		$query_fields .= $fields;
    	}
    	else
    	{
    		$query_fields .= '*';
    	}
    	
    	if (is_array($where_con))
    	{
    		$i_where_array = 0;
    		foreach ($where_con as $condition => $value)
    		{
    			if ($i_where_array == 0)
    			{
    				$query_where .= "WHERE ".$condition." = '".$value."'";
    			}
    			else
    			{
    				$query_where .= "AND ".$condition." = '".$value."'";
    			}
    			$i_where_array++;
    		}
    		
    	}
    	elseif (!empty($where_con) && !empty($where_val))
    	{
    		$query_where .= "WHERE ".$where_con." = '".$where_val."'";
    	}
    	
    	$query = 	'
					SELECT		'.$query_fields.'
					FROM		'.$table.'
					'.$query_where.'
					'.$query_order.'
					'.$query_limit.'
					';
		//echo $query;
		$sql = parent::sql_query($query);
		if (!$sql)
		{
			throw new ErrorHandler('There was an error while retrieivng the data from the database');
		}
		$array = array();
		while ($row = parent::sql_fetch_assoc($sql))
		{
			foreach ($row as $key => $value)
			{
				$array[$i_rows][$key] = $value;
			}
			$i_rows++;
		}
		
    	return $array;
    }
    
    public function sql_get_row($table, $fields = '', $where_con = '', $where_val = '', $limit = 0, $offset = 0, $order_by = '', $order_dir = '')
    {
    	$query_fields 	= '';
    	$query_where 	= '';
    	$query_limit 	= '';
    	$query_order 	= '';
    	$i_rows = 0;
    	
    	// Set the fields
    	if (is_array($fields))
    	{
  			$query_fields .= implode(',', $fields);
    	}
    	elseif (!empty($fields))
    	{
    		$query_fields .= $fields;
    	}
    	else
    	{
    		$query_fields .= '*';
    	}
    	
    	if (is_array($where_con))
    	{
    		$i_where_array = 0;
    		foreach ($where_con as $condition => $value)
    		{
    			if ($i_where_array == 0)
    			{
    				$query_where .= "WHERE ".$condition." = '".$value."'";
    			}
    			else
    			{
    				$query_where .= "AND ".$condition." = '".$value."'";
    			}
    			$i_where_array++;
    		}
    		
    	}
    	elseif (!empty($where_con) && !empty($where_val))
    	{
    		$query_where .= "WHERE ".$where_con." = '".$where_val."'";
    	}
    	
    	$query = 	'
					SELECT		'.$query_fields.'
					FROM		'.$table.'
					'.$query_where.'
					'.$query_order.'
					'.$query_limit.'
					';
		$sql = parent::sql_query($query);
		if (!$sql)
		{
			throw new ErrorHandler('There was an error while retrieivng the data from the database');
		}
		$row = parent::sql_fetch_assoc($sql);		
    	return $row;
    }
 
    /***
     * Function for listing single data from a database
     * @access 	public
     * 
     * Required:
     * - $table: The name of the table where you want to get the data from
     * - $field: The field that you want to use to list data form
     * 
     * Example
     * - $my_data = $mydb->sql_list_data('members', 'id');
     */
    /**
     * Database::sql_list_data()
     * 
     * @param mixed $table
     * @param mixed $fields
     * @param string $where_condition
     * @param string $where_value
     * @return
     */
    public function sql_list_data($table, $fields, $where_condition='', $where_value='')
    {
    	
    	// Check what type of data the fields have gotten
    	if (is_array($fields))
    	{
    		$new_fields	= array();
    		$i_fields	= 0;
    		foreach ($fields as $key)
    		{
    			$new_fields[$i_fields] = trim($key);
    			$i_fields++;
    		}
    		$list_fields = implode(',', $new_fields);
    	}
    	// If it's no array
    	else
    	{
    		// Check for illegal characters
    		if (preg_match('/(\s)/', $fields))
    		{
    			throw new ErrorHandler('Error: Use of illegal characters in query');
    		}
    		
    		// If a comma seperator has been find, we split and clean them up
    		if (preg_match('/,/', $fields))
    		{
    			$list_fields = explode(',', $fields);
    			$new_fields	= array();
    			$i_fields	= 0;
    			foreach($list_fields as $key => $value)
    			{
    				$new_fields[$i_fields] = trim($value);
    				$i_fields++;
    			}
    			
    			// Now put it back together
    			$list_fields = implode(',', $new_fields);
    		}
    	}
    	
    	// Now the conditions
    	if ($where_condition != '' && $where_condition != '')
    	{
    		
    	}
    	
    	// Check if all fields should be used or only one field
    	$fields = (isset($list_fields)) ? $list_fields : $fields;
    	
    	// And now, do the query!
    	$query	=	"
    				SELECT		" . $fields . "
    				FROM		" . $table . "
    				";
    	$sql	=	parent::sql_query($query);
   		$i_row	=	0;
   		$data	=	array();
   		while ($row = parent::sql_fetch_assoc($sql))
   		{
   			if ($fields == $list_fields)
   			{
   				$list_fields = explode(',', $fields);
   				foreach ($list_fields as $key => $value)
   				{
   					$data[$value] = $row[$value];
   				}
   			}
   			
   			$data[$i] = $row[$fields];
   			$i++;
   		}
   		return $data;
    }
    
    /***
     * Function for inserting data in a database table
     * Required:
     * - $table: The name of the table where you want to insert your data into
     * - $data (array): Array containing the fields and values of the data you want to insert
     * 
     * Optional:
     * - $prefix: You may want to do this if all the fieldnames have the same prefix
     * 
     * Example:
     * - $insert = $mydb->sql_insert_row('members', $array_with_data);
     */
    /**
     * Database::sql_insert_row()
     * 
     * @param mixed $table
     * @param mixed $data
     * @param string $prefix
     * @return
     */
    public function sql_insert_row($table, array $data, $prefix='')
    {
    	$list_fields = "";
    	$list_values = "";
    	$list_prefix = (!$prefix == '') ? $prefix : '';
    	foreach ($data as $key => $value)
    	{  
    		$list_fields .= "".$list_prefix.$key.", ";
    		$list_values .= "'".$value."', ";
    	}
    	$x_fields = strlen($list_fields);
    	$x_values = strlen($list_values);
    	
    	// Remove the last comma and space
    	$fields = substr($list_fields, 0, ($x_fields-2));
    	$values = substr($list_values, 0, ($x_values-2));
    	
    	// Do the query
    	$query	=	"
    				INSERT INTO		" . $table . "
    				(" . $fields . ")
    				VALUES
    				(" . $values . ")
    				";
    	$sql	=	parent::sql_query($query);
    	return (int) parent::sql_insert_id();
    }
    
    /***
     * Function for updating data in a database table
     * Required:
     * - $table: The name of the table where you want to update your data
     * - $data (array): Array containing the fields and values of the data you want to insert
     * - $where_condition: The name of the field where the count has to be based on
     * - $where_value: The value given with the condition
     * 
     * Optional:
     * - $prefix: You may want to do this if all the fieldnames have the same prefix
     * 
     * Example:
     * $update = $mydb->sql_update_row('members', $array_with_data, 'member_id', $member_id);
     */
    /**
     * Database::sql_update_row()
     * 
     * @param mixed $table
     * @param mixed $data
     * @param mixed $where_condition
     * @param mixed $where_value
     * @param string $prefix
     * @return
     */
    public function sql_update_row($table, array $data, $where_condition, $where_value, $prefix='')
    {
	    // Check if there is even something to update
    	if ($this->sql_count($table, $where_condition, $where_value) >= '1')
	    {
		   	// Initiate the data
		   	$list_data = '';
		   	
		   	// Check if a prefix needs to be used
		   	$list_prefix = (!$prefix == '') ? $prefix : '';
		   	
		   	// Loop all values together
		   	foreach ($data as $key => $value)
		   	{
		   		$list_data .= "".$list_prefix.$key." = '".$value."', ";
		   	}
		   	
		   	// Get the stringlength of the value loop
		   	$x_data = strlen($list_data);
		   	
		   	// Remove the last comma and space
		   	$values = substr($list_data, 0, ($x_data-2));
		   	
		   	// Do the query
		   	$query	=	"
		   				UPDATE		" . $table . "
						SET			" . $values . "
						WHERE		" . $where_condition . " = '" . $where_value . "'
		   				";
		   	$sql	=	parent::sql_query($query);
			return true;
	    }
	    else
	    {
	    	return false;
	    }
    }
    
    /***
     * Function for deleting data in a database table
     * Required:
     * - $table: The name of the table where you want to update your data
     * - $where_condition: The name of the field where the count has to be based on
     * - $where_value: The value given with the condition
     * 
     * Example
     * $delete = $mydb->sql_delete_row('members', 'id', $member_id);
     */
	/**
	 * Database::sql_delete_row()
	 * 
	 * @param mixed $table
	 * @param mixed $where_condition
	 * @param mixed $where_value
	 * @return
	 */
	public function sql_delete_row($table, $where_condition, $where_value)
    {
	    if ($this->sql_count($table, $where_condition, $where_value) >= '1')
	    {
	    	// Do the query
		    $query	=	"
		    			DELETE FROM	" . $table . "
						WHERE		" . $where_condition . " = '" . $where_value . "'
		    			";
		    $sql	=	parent::sql_query($query);    	
	    	return true;
	    }
	    else
	    {
	    	return false;
	    }
    }
    
    public function sql_swap($table, array $where_conditions, array $where_values, $swap_fields)
    {
    	$i_wcondition 	= 0;
    	$i_wvalue		= 0;
		
		if (is_array($where_conditions) && is_array($where_values))
    	{
   			if (count($where_conditions) == 2 && count($where_values == 2))
   			{
   				foreach ($where_conditions as $key => $value)
   				{
   					$wcondition[$i_wcondition] = $value;
   					$i_wcondition++;
   				}
   				
   				foreach ($where_values as $key => $value)
   				{
   					$wvalue[$i_wvalue] = $value;
   					$i_wvalue++;
   				}
   				
   				
   			}
   			else
   			{
   				
   			}
    	}
    	else
    	{
    		
    	}
    }
}

?>